using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using eShop.Model;
using eShop.Utility;


namespace eShop.DataAccess
{
    public class CMSDAO : DatabaseUtility
    {
        public static Int32 CreateNewTblCMS
        (
            String cmsType,
            String cmsTitle,
            String cmsContent,
            Boolean isActive,
            Boolean isDeleted
        )
        {
            //creating an empty TblCMS object
            CMS tblCMS = new CMS();
            tblCMS.CMSType = (CMSType)Enum.Parse(typeof(CMSType), cmsType);
            tblCMS.CMSTitle = cmsTitle;
            tblCMS.CMSContent = cmsContent;
            tblCMS.IsActive = isActive;
            tblCMS.IsDeleted = isDeleted;

            return CMSDAO.CreateNewTblCMS(tblCMS);
        }

        public static bool UpdateTblCMS
        (
            Int32 cmsId,
            String cmsType,
            String cmsTitle,
            String cmsContent,
            Boolean isActive,
            Boolean isDeleted
        )
        {
            CMS tblCMS = GetTblCMSByCmsId(cmsId);

            tblCMS.CMSType = (CMSType)Enum.Parse(typeof(CMSType), cmsType);
            tblCMS.CMSTitle = cmsTitle;
            tblCMS.CMSContent = cmsContent;
            tblCMS.IsActive = isActive;
            tblCMS.IsDeleted = isDeleted;

            return CMSDAO.UpdateTblCMS(tblCMS);
        }

        public static bool DeleteTblCMS
        (
            Int32 cmsId
        )
        {
            //Validate Input
            if (cmsId <= GetCmsIdMinValue)
                throw (new ArgumentOutOfRangeException("cmsId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@CmsId", SqlDbType.Int, 0, ParameterDirection.Input, cmsId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLCMS_DELETETBLCMS);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }


        public static bool DeleteTblCMSList(System.Collections.Generic.List<Int32> cmsIdListToDelete)
        {
            //Validate Input
            foreach (Int32 cmsId in cmsIdListToDelete)
                if (cmsId <= GetCmsIdMinValue)
                    throw (new ArgumentOutOfRangeException("cmsId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(cmsIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@CmsIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLCMS_DELETETBLCMSLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == cmsIdListToDelete.Count ? true : false);
        }

        public static CMS GetTblCMSByCmsId(Int32 cmsId)
        {
            if (cmsId <= GetCmsIdMinValue)
                throw (new ArgumentOutOfRangeException("cmsId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CmsId", SqlDbType.Int, 0, ParameterDirection.Input, cmsId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLCMS_GETTBLCMSBYCMSID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblCMSCollectionFromReader);
            List<CMS> objCollection = ((List<CMS>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }


        public static List<CMS> GetAllTblCMS()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLCMS_GETALLTBLCMS);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblCMSCollectionFromReader);
            List<CMS> objCollection = ((List<CMS>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static List<CMS> GetAllTblCMSPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "CmsId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLCMS_GETALLTBLCMSPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblCMSCollectionFromReader);
            List<CMS> objCollection = ((List<CMS>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllTblCMSPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLCMS_GETALLTBLCMSPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static bool Save(CMS cms)
        {
            if (cms.CMSId <= GetCmsIdMinValue)
            {
                Int32 TempId = CreateNewTblCMS(cms);
                if (TempId > 0)
                {
                    cms.CMSId = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblCMS(cms));
            }

        }

        public static Int32 CreateNewTblCMS(CMS newTblCMS)
        {
            // validate Parameters
            if (newTblCMS == null)
                throw (new ArgumentNullException("newTblCMS"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@CmsType", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblCMS.CMSType.ToString());
            AddParameterToSqlCmd(sqlCmd, "@CmsTitle", SqlDbType.NVarChar, 100, ParameterDirection.Input, newTblCMS.CMSTitle);
            AddParameterToSqlCmd(sqlCmd, "@CmsContent", SqlDbType.NText, -1, ParameterDirection.Input, newTblCMS.CMSContent);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblCMS.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, newTblCMS.IsDeleted);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLCMS_CREATENEWTBLCMS);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }

        public static bool UpdateTblCMS(CMS newTblCMS)
        {
            // validate Parameters
            if (newTblCMS == null)
                throw (new ArgumentNullException("newTblCMS"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@CmsId", SqlDbType.Int, 0, ParameterDirection.Input, newTblCMS.CMSId);
            AddParameterToSqlCmd(sqlCmd, "@CmsType", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblCMS.CMSType.ToString());
            AddParameterToSqlCmd(sqlCmd, "@CmsTitle", SqlDbType.NVarChar, 100, ParameterDirection.Input, newTblCMS.CMSTitle);
            AddParameterToSqlCmd(sqlCmd, "@CmsContent", SqlDbType.NText, -1, ParameterDirection.Input, newTblCMS.CMSContent);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblCMS.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, newTblCMS.IsDeleted);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLCMS_UPDATETBLCMS);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static IList GenerateTblCMSCollectionFromReader(IDataReader returnData)
        {
            //creating the instance of Employee collection
            List<CMS> colTblCMS = new List<CMS>();

            while (returnData.Read())
            {
                //passing the TblCMS constructor parameters from the current instance of data reader fields.
                CMS newTblCMS = new CMS()
                {
                    CMSId = returnData["CmsId"] == System.DBNull.Value ? GetCmsIdMinValue : (Int32)returnData["CmsId"],
                    CMSType = returnData["CmsType"] == System.DBNull.Value ? CMSType.None : (CMSType)Enum.Parse(typeof(CMSType), (string)returnData["CmsType"]),
                    CMSTitle = returnData["CmsTitle"] == System.DBNull.Value ? string.Empty : (String)returnData["CmsTitle"],
                    CMSContent = returnData["CmsContent"] == System.DBNull.Value ? null : (String)returnData["CmsContent"],
                    IsActive = returnData["IsActive"] == System.DBNull.Value ? false : (Boolean)returnData["IsActive"],
                    IsDeleted = returnData["IsDeleted"] == System.DBNull.Value ? false : (Boolean)returnData["IsDeleted"]

                };
                //adding the Employee to the collection
                colTblCMS.Add(newTblCMS);
            }

            //returns the collection of Companys 
            return (colTblCMS);
        }

        public static Int32 GetCmsIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SP_TBLCMS_CREATENEWTBLCMS = "sp_TblCMS_CreateNewTblCMS";
        internal const string SP_TBLCMS_UPDATETBLCMS = "sp_TblCMS_UpdateTblCMS";
        internal const string SP_TBLCMS_DELETETBLCMS = "sp_TblCMS_DeleteTblCMS";
        internal const string SP_TBLCMS_DELETETBLCMSLIST = "sp_TblCMS_DeleteTblCMSList";
        internal const string SP_TBLCMS_GETTBLCMSBYCMSID = "sp_TblCMS_GetTblCMSByCmsId";
        internal const string SP_TBLCMS_GETALLTBLCMS = "sp_TblCMS_GetAllTblCMS";
        internal const string SP_TBLCMS_GETALLTBLCMSPAGED = "sp_TblCMS_GetAllTblCMSPaged";
        internal const string SP_TBLCMS_GETALLTBLCMSPAGEDCOUNT = "sp_TblCMS_GetAllTblCMSPagedCount";

        public static List<CMS> GetCMSByCmsType(string CmsType)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CmsType", SqlDbType.NVarChar, 50, ParameterDirection.Input, CmsType);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_CMS_GET_CMS_BY_CMSTYPE);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblCMSCollectionFromReader);
            List<CMS> objCollection = ((List<CMS>)ExecuteReaderCmd(sqlCmd, reader));
            return objCollection;
        }

        public static List<CMS> GetCMSByCmsID(int cmsID)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CmsID", SqlDbType.Int, 4, ParameterDirection.Input, cmsID);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_CMS_GET_CMS_BY_CMSID);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblCMSCollectionFromReader);
            List<CMS> objCollection = ((List<CMS>)ExecuteReaderCmd(sqlCmd, reader));
            return objCollection;
        }

        private const string SP_TBL_CMS_GET_CMS_BY_CMSTYPE = "sp_TblCMS_Get_CMS_By_CmsType";
        private const string SP_TBL_CMS_GET_CMS_BY_CMSID = "sp_TblCMS_Get_CMS_By_CmsID";
    }
}
